<!DOCTYPE html>
<html lang="en-us">
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">

  <title> MySQL </title>

  <link rel="stylesheet" media="screen" href="/css/style.css" >
</head>
<body>

  <header class="site-header">
    <h3 class="site-title">
      <a href="https://mkaz.github.io/working-with-go/">
        Working with Go
      </a>
    </h3>
  </header>

  <main>

    <aside>
      <h2>Contents</h2>
      <ul>
        
          <li><a href="/hello.html">Hola Mundo</a></li>
        
          <li><a href="/strings.html">Strings</a></li>
        
          <li><a href="/slices.html">Slices</a></li>
        
          <li><a href="/functions.html">Functions</a></li>
        
          <li><a href="/control-structures.html">Control Structures</a></li>
        
          <li><a href="/for-loops.html">For Loops</a></li>
        
          <li><a href="/structs.html">Structs</a></li>
        
          <li><a href="/maps.html">Maps</a></li>
        
          <li><a href="/files.html">Files</a></li>
        
          <li><a href="/read-dir.html">Directories</a></li>
        
          <li><a href="/objects.html">Objects</a></li>
        
          <li><a href="/command-line.html">Command-line</a></li>
        
          <li><a href="/date-time.html">Date/Time</a></li>
        
          <li><a href="/sorting.html">Sorting</a></li>
        
          <li><a href="/json.html">JSON</a></li>
        
          <li><a href="/web-server.html">Web Server</a></li>
        
          <li><a href="/guess-game.html">Guessing Game</a></li>
        
          <li><a href="/rest-api.html">REST API</a></li>
        
          <li><a href="/regexps.html">Regexp</a></li>
        
          <li><a href="/memcache.html">Memcache</a></li>
        
          <li><a href="/mysql.html">MySQL</a></li>
        
          <li><a href="/interfaces.html">Interfaces</a></li>
        
          <li><a href="/testing.html">Testing</a></li>
        
      </ul>
    </aside>

    <article class="entry-content">
      <h1>Working with MySQL Database</h1>

<p>This is an example file for working with databases. Go provides an abstract datalayer using the <a href="https://golang.org/pkg/database/sql/"><code>database/sql</code></a> standard library. You need to load the specific driver your database first, than working with it is relatively the same.</p>

<p>This example uses the <a href="https://github.com/go-sql-driver/mysql"><code>go-sql-driver/mysql</code></a>.</p>

<h2>Example Tables</h2>

<p>You can create example table and data with the sql query below</p>

<pre><code class="language-sql">DROP TABLE IF EXISTS `posts`;

CREATE TABLE `posts` (
  `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `posts` (`id`, `title`, `body`)
VALUES
	(1,'Hello World','The content of the hello world'),
	(2,'Hello Second World','The content of the hello second world');

</code></pre>

<h2>Example Program</h2>

<pre><code class="language-go">// standard main package
package main

// Necessary packages to work with mysql databases
import (
	&quot;database/sql&quot;
	&quot;fmt&quot;

	_ &quot;github.com/go-sql-driver/mysql&quot;
)

// dbConn connects to the database
func dbConn() (db *sql.DB) {

	dbDriver := &quot;mysql&quot;
	dbUser := &quot;root&quot;
	dbPass := &quot;root&quot;
	dbName := &quot;godb&quot;
	db, err := sql.Open(dbDriver, dbUser+&quot;:&quot;+dbPass+&quot;@/&quot;+dbName)
	if err != nil {
		panic(err.Error())
	}
	return db
}

// Post is the key to connect database to the program
type Post struct {
	Id    int
	Title string
	Body  string
}

// getAll gets all the records from database
func getAll() {

	db := dbConn()

	selDB, err := db.Query(&quot;SELECT * FROM Posts ORDER BY id DESC&quot;)
	if err != nil {
		panic(err.Error())
	}

	post := Post{}
	posts := []Post{}

	for selDB.Next() {

		var id int
		var title, body string

		err = selDB.Scan(&amp;id, &amp;title, &amp;body)
		if err != nil {
			panic(err.Error())
		}

		post.Id = id
		post.Title = title
		post.Body = body

		posts = append(posts, post)
	}

	for _, post := range posts {
		fmt.Println(post.Title)
	}

	defer db.Close()
}

// getOne gets only one record from database
func getOne(postId int) {

	db := dbConn()

	selDB, err := db.Query(&quot;SELECT * FROM Posts WHERE id=?&quot;, postId)
	if err != nil {
		panic(err.Error())
	}

	post := Post{}

	for selDB.Next() {

		var id int
		var title, body string

		err = selDB.Scan(&amp;id, &amp;title, &amp;body)
		if err != nil {
			panic(err.Error())
		}

		post.Id = id
		post.Title = title
		post.Body = body

	}

	fmt.Println(&quot;Post Title	: &quot; + post.Title)
	fmt.Println(&quot;Post Body	: &quot; + post.Body)

	defer db.Close()
}

// add helps you to add new record to database
func add() {

	db := dbConn()

	title := &quot;Hello Second World&quot;
	body := &quot;The content of the hello second world&quot;
	insertQuery, err := db.Prepare(&quot;INSERT INTO Posts(title, body) VALUES(?,?)&quot;)
	if err != nil {
		panic(err.Error())
	}

	insertQuery.Exec(title, body)

	fmt.Println(&quot;ADDED: Title: &quot; + title + &quot; | Body: &quot; + body)

	defer db.Close()

}

// update helps you to update an existing record in the database
func update(postId int) {

	db := dbConn()

	title := &quot;Hello 1 World&quot;
	body := &quot;The content of the hello 1 world&quot;
	updateQuery, err := db.Prepare(&quot;UPDATE Posts SET title=?, body=? WHERE id=?&quot;)
	if err != nil {
		panic(err.Error())
	}

	updateQuery.Exec(title, body, postId)

	fmt.Println(&quot;UPDATED: Title: &quot; + title + &quot; | Body: &quot; + body)

	defer db.Close()

}

// delete helps you to delete an existing record in the database
func delete(postId int) {

	db := dbConn()

	deleteQuery, err := db.Prepare(&quot;DELETE FROM Posts WHERE id=?&quot;)
	if err != nil {
		panic(err.Error())
	}

	deleteQuery.Exec(postId)

	fmt.Println(&quot;DELETED&quot;)

	defer db.Close()

}

func main() {

	//add()
	//update(1)
	//delete(1)
	//getOne(1)
	getAll()

}
</code></pre>

    </article>

  </main>

<footer class="site-footer" role="contentinfo">
    An <a href="https://mkaz.blog/">mkaz</a> joint
    <span class="sep"> | </span>
    <a href="https://github.com/mkaz/hasite">Powered by Hastie</a>
</footer>

<script src="/js/asciinema/asciinema-player.js"></script>
<script src="/js/prism.js"></script>

</body>
</html>